package net.bwie.realtime.dws.log.function;


import com.google.common.cache.CacheBuilder;
import com.google.common.cache.CacheLoader;
import com.google.common.cache.LoadingCache;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;

public class MonitorLimitSpeedCache {
    private static final Logger LOG = LoggerFactory.getLogger(MonitorLimitSpeedCache.class);

    // 数据库连接信息，建议从配置文件读取
    private static final String JDBC_URL = "jdbc:mysql://node101:3306/vehicle_db?useSSL=false&serverTimezone=UTC";
    private static final String JDBC_USER = "root";
    private static final String JDBC_PASSWORD = "123456";

    // 城市默认限速
    private static final int DEFAULT_LIMIT_SPEED = 60;

    // Guava缓存配置
    private static final LoadingCache<String, Integer> MONITOR_LIMIT_SPEED_CACHE = CacheBuilder.newBuilder()
            .maximumSize(10000)        // 最大缓存条目
            .expireAfterWrite(30, TimeUnit.MINUTES)  // 写入后30分钟过期
            .refreshAfterWrite(10, TimeUnit.MINUTES) // 10分钟刷新一次
            .build(new CacheLoader<String, Integer>() {
                @Override
                public Integer load(String monitorId) throws Exception {
                    // 从数据库加载数据
                    return loadFromDB(monitorId);
                }
            });

    // 从数据库加载卡口限速
    public static Integer loadFromDB(String monitorId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
            String sql = "SELECT limit_speed FROM monitor_limit_speed WHERE monitor_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, monitorId);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("limit_speed");
            }
        } catch (Exception e) {
            LOG.error("Failed to load limit speed for monitor: " + monitorId, e);
        } finally {
            // 关闭资源
            try {
                if (rs != null) {
                    rs.close();
                };

                if (pstmt != null) {
                    pstmt.close();
                };

                if (conn != null) {
                    conn.close();
                };
            } catch (Exception e) {
                LOG.error("Failed to close database resources", e);
            }
        }

        // 如果没有找到，返回默认限速
        return DEFAULT_LIMIT_SPEED;
    }

    // 获取卡口限速
    public static Integer getLimitSpeed(String monitorId) {
        try {
            return MONITOR_LIMIT_SPEED_CACHE.get(monitorId);
        } catch (ExecutionException e) {
            LOG.error("Failed to get limit speed for monitor: " + monitorId, e);
            return DEFAULT_LIMIT_SPEED;
        }
    }
}
